package dao.impl;

import core.Page;
import dao.ISignInDao;
import domain.SignRecord;
import util.JdbcUtils;

import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


public class SignInDaoImpl implements ISignInDao {


    @Override
    public int addSignRecord(SignRecord signRecord) throws SQLException {
        Connection conn = JdbcUtils.getConnection();
        String sql = "insert into sign_in_record (student_id,sign_time) values (?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        preparedStatement.setInt(i++, signRecord.getStudentId());
        //转换日期类型为Timestamp
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        preparedStatement.setTimestamp(i,Timestamp.valueOf(sdf.format(signRecord.getSignTime())));
        int result = preparedStatement.executeUpdate();
        preparedStatement.close();
        conn.close();
        return result;
    }

    @Override
    public SignRecord getSignTimeByStudentId(String studentid) throws SQLException {
        return null;
    }

    @Override
    public List<SignRecord> selectSignRecord(SignRecord signRecord) throws SQLException {
        List<SignRecord> signRecordList = new ArrayList<>();
        Connection conn = JdbcUtils.getConnection();
        String sql = "select * from sign_in_record where student_id = ?";
        if (signRecord.getStartTime()!=null&&signRecord.getEndTime()!=null){
            sql = sql+" and sign_time between ? and ? limit ? , ?";
        }else {
            sql = sql+" limit ? , ?";
        }
        PreparedStatement preparedStatement = conn.prepareStatement(sql);
        int i = 1;
        int totleI = 1;
        preparedStatement.setInt(i++,signRecord.getStudentId());
         if (signRecord.getStartTime()!=null&&signRecord.getEndTime()!=null){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            preparedStatement.setTimestamp(i++,Timestamp.valueOf(sdf.format(signRecord.getStartTime())));
            preparedStatement.setTimestamp(i++,Timestamp.valueOf(sdf.format(signRecord.getEndTime())));
            preparedStatement.setInt(i++,signRecord.getPageSize());
            preparedStatement.setInt(i,signRecord.getPageNum());
        }else {
            preparedStatement.setInt(i++,signRecord.getPageSize());
            preparedStatement.setInt(i,signRecord.getPageNum());
         }
        ResultSet rs = preparedStatement.executeQuery();
        while (rs.next()) {
                SignRecord signRecord1 = new SignRecord();
                signRecord1.setRecordId(rs.getInt("id"));
                signRecord1.setStudentId(rs.getInt("student_id"));
                signRecord1.setSignTime(rs.getTimestamp("sign_time"));
                signRecordList.add(signRecord1);
            }
        preparedStatement.close();
        conn.close();
        return signRecordList;
    }

    @Override
    public int selectTotal(SignRecord signRecord) throws SQLException{
        Integer total = 0;
        Connection conn = JdbcUtils.getConnection();
        String totalSql = "select count(1) from sign_in_record where student_id = ?";
        if (signRecord.getStartTime()!=null&&signRecord.getEndTime()!=null){
            totalSql = totalSql+" and sign_time between ? and ? ";
        }
        PreparedStatement preparedStatementT = conn.prepareStatement(totalSql);
        int totleI = 1;
        preparedStatementT.setInt(totleI++,signRecord.getStudentId());
         if (signRecord.getStartTime()!=null&&signRecord.getEndTime()!=null){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            preparedStatementT.setTimestamp(totleI++,Timestamp.valueOf(sdf.format(signRecord.getStartTime())));
            preparedStatementT.setTimestamp(totleI,Timestamp.valueOf(sdf.format(signRecord.getEndTime())));
        }
         ResultSet rsT = preparedStatementT.executeQuery();
        while (rsT.next()) {
               total= rsT.getInt(1);
            }
        preparedStatementT.close();
        conn.close();
        return total;
    }
}
